package org.frasermccrossan.toneitdown;

import java.util.Locale;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.media.AudioManager;

class ProfileOpenHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 3;
    private static final String DATABASE_NAME = "profiledb";
    static final String PROFILE_TABLE_NAME = "profiles";
    static final String NAME = "name";
    static final String VOLUME = "volume";
    static final String VIBRATE = "vibrate";
    static final String STATUS_TABLE_NAME = "status";
    static final String CURRENT_PROFILE = "current_profile";
    static final String TIMED_PROFILE = "timed_profile";
    static final String TIMED_EXPIRY = "timed_expiry";
    static final long NULL_PROFILE = -1;
    static final String HISTORY_TABLE_NAME = "history";
    static final String HOUR_OF_DAY = "hour_of_day";
    static final String MINUTE = "minute";
    static final String LAST_USED = "last_used";
    static final int MAX_RECORDED_TIMES = 5;

    private Context c;
    
	ProfileOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        c = context;
    }

	@Override
	public void onCreate(SQLiteDatabase db) {
		String s;
		s = String.format("CREATE TABLE %s (_id integer primary key, %s TEXT, %s NUMBER, %s BOOLEAN);",
        		PROFILE_TABLE_NAME, NAME, VOLUME, VIBRATE);
        db.execSQL(s);
        // find the maximum stream volume and scale out initial entries
		AudioManager am = (AudioManager) c.getSystemService(Context.AUDIO_SERVICE);
		int maxVolume = am.getStreamMaxVolume(AudioManager.STREAM_RING);
        final Profile[] default_profiles = {
        	new Profile(c.getString(R.string.normal), 75 * maxVolume /100, 0),
        	new Profile(c.getString(R.string.outdoor), maxVolume, 1),
        	new Profile(c.getString(R.string.quiet), 20 * maxVolume / 100, 1),
        	new Profile(c.getString(R.string.silent), 0, 1)
        };
        for (int i = 0; i < default_profiles.length; ++i) {
        	s = String.format(Locale.US, "insert into %s (name, volume, vibrate) values ('%s', %d, %d)",
       			 PROFILE_TABLE_NAME,
                 default_profiles[i].name,
                 default_profiles[i].volume,
                 default_profiles[i].vibrate);
        	db.execSQL(s);
        }
        s = String.format("CREATE TABLE %s as select %d as %s, %d as %s;",
				STATUS_TABLE_NAME, NULL_PROFILE, CURRENT_PROFILE, NULL_PROFILE, TIMED_PROFILE);
        db.execSQL(s);
        // for first-time users
        onUpgrade(db, 1, DATABASE_VERSION);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int v1, int v2) {
		int v;
		for (v = v1 + 1; v <= v2; ++v) {
			upgradeTo(db, v);
		}
	}

	private void upgradeTo(SQLiteDatabase db, int version) {
		switch(version) {
		case 2:
			upgrade1to2(db);
			break;
		case 3:
			upgrade2to3(db);
			break;
		}
	}
	
	private void upgrade1to2(SQLiteDatabase db) {
		String s = String.format("alter table %s add column %s number", STATUS_TABLE_NAME, TIMED_EXPIRY);
		db.execSQL(s);
	}
	
	private void upgrade2to3(SQLiteDatabase db) {
		String s = String.format("create table %s (_id integer primary key, %s number, %s number, %s number)",
				HISTORY_TABLE_NAME, HOUR_OF_DAY, MINUTE, LAST_USED);
		db.execSQL(s);
	}
	
}
